ارسال اطلاعات از اکسل به اکسس به کمک کد نویسی VBA
اگر شما هم مثل من با نرم افزار های اکسس و اکسل بطور همزمان کار کرده باشید با من هم عقیده هستید که بین این دو نرم افزار شباهت هایی وجود دارد .
هر چند که زمینه کاری این دو نرم افزار کاملا از هم مجزاست چون اکسل یک نرم افزار محاسباتی است و اکسس نرم افزاری است برای مدیریت بانکهای اطلاعاتی .
اما خیلی از کاربران بواسطه همین شباهت هایی که بین این دو نرم افزار وجود دارد برخی از مواقع این دو نرم افزار را به جای یکدیگر استفاده می کنند .
البته که در این بین نرم افزار اکسس قربانی خواهد بود .چون هم کار کردن با نرم افزار اکسل به نسبت ساده تر از اکسس است و هم اینکه میزان سواد کاری خیلی از افراد برای کار با اکسس در حد بسیار کمی است .
اما عکس این قضیه خیلی کم اتفاق می افتند . بعنی اینکه کسی در کار با اکسس وارد باشد و بخواهد برای ایجاد یک بانک اطلاعاتی به سراغ اکسل برود جزو محالات روزگار است .
از طرفی هم یکی از ویژگی های بسیار مهم و کاربردی که هم برای اکسل وجود دارد و هم برای اکسس این است که ما به راحتی می توانیم اطلاعات خودمان را بین این دو نرم افزار جابجا کنیم .
یعنی اگر اطلاعاتی در اکسل داشته باشیم به راحتی می توانیم این اطلاعات را به نرم افزار اکسس منتقل کنیم و بر عکس هم اگر اطلاعاتی در اکسس داشته باشیم به راحتی می توانیم آنها را به نرم افزار اکسل منتقل کنیم .
اما چه جوری و به چه نحو می شود این کار را انجام داد ؟ این دقیقا همان چیزی است که من و شما می خواهیم در این مطلب به آن بپردازیم و ببینیم که چه راهکارهایی برای این کار وجود دارد .
من علیرضا شهرآئینی هستم . خوشحال می شوم اگر من را در این مطلب همراهی کنی تا با هم یاد بگیریم که از چه روش یا روش هایی می شود اطلاعات خود را از داخل نرم افزار اکسل به نرم افزار اکسس منتقل کنیم.
معرفی روش های ارسال اطلاعات از اکسل به اکسس
بطور کل ما برای انتقال اطلاعات خود از داخل نرم افزار اکسل به نرم افزار اکسس دو راه پیش رو داریم .
راه اول این است که ما از داخل نرم افزار اکسس اقدام به دریافت اطلاعات مورد نظر که در داخل یک فایل اکسل وجود دارد بکنیم . این در حالتی است که ما در داخل نرم افزار اکسس به سر ببریم .
خود این کار یعنی اینکه ما در داخل نرم افزار اکسس باشیم و اطلاعات مورد نظر را از داخل فایل اکسل استخراج و به اکسس خود منتقل کنیم شامل یک سری روش هایی است که در اینجا به آن نمی خواهم بپردازیم .
چون آموزش این کار شامل روش هایی است که بررسی و استفاده از این روش ها خودش احتیاج به یک مطلب آموزشی مجزا دارد که در آینده راجع به آن مطلب جداگانه ای را در خصوص این موضوع در سایت قرار خواهم داد .
ولی امروز می خواهم با هم به بررسی روشی بپردازیم که با استفاده از آن می توانیم اطلاعات خود را در حالتی که در داخل نرم افزار اکسل به سر می بریم برای نرم افزار اکسس بفرستیم .
به عبارتی دیگر آموزش امروز ما در خصوص خروجی گرفتن اطلاعات اکسل است برای نرم افزار اکسس در حالتی که در داخل خود نرم افزار اکسل در حال کار هستیم .
پس با هم برویم و با روش کار آشنا شویم .
طبق معمول همه چیز را با مثال عملی شروع می کنیم . اگر به تصویر زیر نگاهی بکنید متوجه می شوید که من در داخل نرم افزار اکسل یک سری از اطلاعات فرضی را برای خودم آماده کرده ام .
حالا اگر بخواهم این اطلاعات را به نرم افزار اکسس منتقل بکنم به نظر شما چه راهکاری برای آن وجود دارد ؟ آیا تا به حال به آن فکر کرده اید؟
اگر که برای این مشکل فکری کرده اید که راهکاری را برای این کار پیدا کرده اید که دیگر نیازی به مطالعه ادامه این آموزش ندارید . اما اگر برای شما هم جای سوال است که چه جوری می شود این کار را انجام داد کافی است ادامه مطلب را بخوانید .
به امکانات خود اکسل دل نبندید .
اکسل نرم افزار خیلی قدرتمند و به درد بخوری است . برای هر مشکل و مساله ای هم راه حلی دارد . اما برای این مشکل ما راه حل مستقیمی از طرف اکسل وجود ندارد .
یعنی اگر در بین سربرگ ها و گزینه های موجود در آنها به دنبال گزینه ای بگردید که با استفاده از آن بخواهید اطلاعات خود را از اکسل به اکسس منتقل کنید، باید به شما بگویم که همچین گزینه ای وجود ندارد .
اما برای عکس این قضیه اکسل یک راه حل حاضر و آماده دارد. یعنی اگر بخواهید اطلاعاتی را از داخل نرم افزار اکسس به داخل نرم افزار اکسل خود بیاورید خود اکسل به راحتی این کار را برای شما انجام می دهد .
برای این کار کافی است که به سربرگ Data رفته و در داشبورد Get & Transform Data بر روی آیکون Get Data کلیک کنید .از زیر مجموعه این گزینه آیتم From Database را انتخاب کنید .
با انتخاب و کلیک بر روی این گزینه لیستی از نرم افزار های مربوط به مدیریت بانکهای اطلاعاتی برای شما به نمایش در می آید که می توانید گزینه From Microsoft Access Database را بزنید .
بقیه کارها را طبق راهنمایی هایی که برنامه خواهد کرد به راحتی می توانید دنبال کنید تا در انتهای کار اطلاعات مورد نظر خود را از داخل نرم افزار اکسس به داخل نرم افزار اکسل خود منتقل کنید .
اما در اینجا ما به دنبال این هستیم که اطلاعات خود را از اکسل به نرم افزار اکسس خود بفرستیم . اینکه چرا اکسل برای خروجی گرفتن از اطلاعات و فرستادن آنها به سمت اکسس گزینه ای ندارد را من که نمی دانم .
پس چاره کار چیست ؟ آیا باید بی خیال همه چیز شویم ؟ یعنی هیچ راهی برای این کار وجود ندارد ؟ یعنی همه چیز اینجا تمام شده است ؟
اما نه صبر کنید مثل اینکه یک راه وجود دارد . درست است که خود اکسل راهی پیش روی ما نمی گذارد اما مثل اینکه اینجا برنامه نویسی VBA حرف هایی برای گفتن دارد .
یعنی می شود با استفاده از کد ها و برنامه نویسی وی بی ای کاری کرد که اطلاعات خودمان را از اکسل به سمت اکسس ارسال کنیم .؟
آیا این کار شدنی است ؟ و آیا انجام این کار سخت نیست ؟
پاسخ هر دو این سوال ها هم بله است هم نه . پاسخ سوال اول بله است . چون که انجام این کار از طریق کد نویسی VBA کاملا شدنی است و نه هم پاسخ سوال دوم است چون که انجام این کار با استفاده از کد نویسی اصلا سخت نیست .
خوب شما دوست عزیز حالا که تا به اینجای مطلب را مطالعه کرده ای می خواهی بدانی که چه جوری و با استفاده از چه کدهایی می شود این کار را انجام داد . اگر پاسخ شما به این سوال مثبت است پس می توانی ادامه مطلب را مطالعه کنی.
استفاده از کدهای VBA برای ارسال اطلاعات از نرم افزار اکسل به نرم افزار اکسس
اکسل که در این زمینه کاری برای ما نکرد می خواهیم ببینیم که کد های .ی بی ای چه کمکی در این زمینه برای ما می توانند بکنند .
در برنامه نویسی VBA ما دستوری داریم تحت عنوان DoCmd.TransferSpreadsheet .
فکر می کنم که این دستور می تواند کمک خوبی در این زمینه به ما بکند . کمی بعد بیشتر با این دستور آشنا می شویم . اما قبل از آن می خواهم در مورد دو حالت و یا احتمال با شما صحبت کنم .
ببینید دوستان ما در یک طرف نرم افزار اکسلی داریم که حاوی اطلاعات است و ما می خواهیم این اطلاعات را به نرم افزار اکسس خود منتقل کنیم . پس در این سمت مشکلی نداریم .
اما در سمت دیگر که نرم افزار اکسس قرار دارد دو حالت به وجود می آید حالت اول اینکه از قبل ما یک فایل تحت اکسس ایجاد کرده ایم و این فایل آماده دریافت اطلاعات از طرف اکسل است .
اما حالت دوم این است که ما اصلا فایل اکسسی از قبل نداریم بنابراین اینجا باید هم فایل اکسس را ایجاد کنیم و هم اطلاعات خود را از اکسل به آن منتقل کنیم .
هر کدام از این حالت ها برای خودش داستان جداگانه ای دارد . اما من در این جا حالت اول را در نظر می گیرم . یعنی فرض می کنم که از قبل یک قبل اکسس دارم و حالا می خواهم از اکسل اطلاعاتم را در آن وارد کنم .
اول باید مشخص کنم که این فایل اکسس من به چه نامی و در چه آدرسی از کامپیوتر من ذخیره شده است .
در اینجا من اسم فایل اکسس خود را mysoftpluse.accdb و آدرس محل ذخیره سازی آن را هم در Desktop در نظر گرفته ام .
پس تا به اینجای کار همه چیز آماده است . در یک طرف فایل اکسلی دارم به همراه اطلاعات داخل آن و در طرف دیگر هم که فایل اکسسی دارم که قرار اطلاعات را به آن منتقل کنم .
حالا با هم برویم سراغ مراحل انجام این کار با استفاده از کدهای VBA .
مرحله اول برقراری ارتباط بین دو نرم افزار
حالا ما در دو طرف ماجرا دو نرم افزار داریم که باید به نحوی بین دو ارتباط برقرار کنیم . خوب برای این کار به سراغ کدهای وی بی ای می رویم و با هم قدم به قدم پیش می رویم .
برای شروع کار یک متغیر تعریف می کنیم تا آدرس و مسیر فایل اکسس خود را در آن ذخیره کنیم .
Dim str_path As Strings
بعدا این متغیر را مقدار دهی خواهیم کرد . حالا به یک متغیر دیگر هم احتیاج داریم تا به کمک آن نرم افزار اکسس را در داخل اکسل معرفی کنیم . این متغیر در واقع یک نوع Object است .
Dim obj_access As Access.Application
خوب تا به اینجا ما دو متغیر مورد نظر خود را ایجاد کردیم . حالا می رویم و متغیرهای خود را مقدار دهی می کنیم . در متغیر اول آدرس فایل اکسس خود را که قبلا ایجاد کرده ایم را قرار می دهیم .
"str_path = "C:\Users\shahr-pc\Desktop/mysoftpluse.accdb
برای متغیر دوم که که گفتم از نوع Object است شی اکسس را به آن معرفی می کنیم .
Set obj_access = New Access.Application
تا به اینجا ما یک شی داریم از نوع اکسس و یک فایل اکسس داریم که آدرس آن را در یک متغیر ذخیره کرده ایم . حالا کاری می کنیم که همین شی اکسس ، فایل مورد نظر ما از محل آدرسی که در اختیارش می گذاریم را باز کند .
( obj_access.OpenCurrentDatabase (str_path
با این خط از کد ما توانسته ایم فایل اکسس را که آدرس آن را به برنامه داده ایم را در حافظه کامپیوتر خود بار گذاری کنیم . اما باید آن را برای کاربر بر روی صفحه مانیتور خود هم نمایش دهیم .
برای این کار از کد زیر استفاده می کنیم .
obj_access.Visible = True
در اینجا تمام آنچه را که مورد نیاز داشتیم را انجام دادیم . حالا بیایید مجموعه کدهایی را که تا به اینجا نوشتیم را یک جا و در قالب یک روال جمع کنیم .
()Sub tranferingdata
Dim str_path As String
Dim obj_access As Access.Application
"str_path = "C:\Users\shahr-pc\Desktop/mysoftpluse.accdb
Set obj_access = New Access.Application
( obj_access.OpenCurrentDatabase (str_path
obj_access.Visible = True
End Sub
حالا همین روال به همراه کدهای موجود در آن را اجرا کنید . من در این روال از برنامه چه خواسته ام ؟ در این روال از برنامه می خواهم که فایل اکسس مورد نظر من را از محل آدرس معرفی شده خوانده و آن را برای من باز کند .
پس من انتظار دارم که یک فایل اکسس برای من باز شود .اما من به شما می گویم که اگر این کدها را اجرا کنیم فقط یک چیز برای من به نمایش در خواهد آمد و آن هم یک پیغام خطاست .
شاید می پرسی که چرا پیغام خطا ؟ ظاهر قضیه که همه چیز را درست نشان می دهد .
بله همه کدها به درستی وارد شده اند و در کد نویسی ما مشکلی وجود ندارد . فقط این کدها یک چیز را کم دارند و آن هم کتابخانه شی اکسس است .
ما در این کدها یک شی به نام اکسس را به نرم افزار اکسل خود معرفی کرده ایم اما اکسل هیچ اطلاعاتی از شی ای به نام اکسس ندارد این اطلاعات در داخل یک کتابخانه قرار دارد که باید آن را در دسترس اکسل خود بگذاریم .
برای این کار از منوی Option بر روی گزینه Refrences کلیک کنید . در پنجره ای که برای شما به نمایش در می آید آنقدر به سمت پایین اسکرول کنید تا گزینه ای به نام microsoft Access Object Library را پیدا کنید .
فقط کافی است تیک این گزینه را بزنید و دکمه OK را تائید کنید . حالا همه چیز تمام شده است . این بار کدهای خود را اجرا کنید و نتیجه کار را ببینید . نتیجه همان چیزی است که انتظارش را داشتیم مگر نه ؟
خوب این مرحله اول از کار بود که با استفاده از ان بین نرم افزار اکسل و اکسس خود ارتباط برقرار کردیم . حالا باید برویم به سراغ اینکه چه جوری می شود اطلاعات خود را از اکسل به نرم افزار اکسس مورد نظر منتقل کنیم .
مرحله دوم : نحوه انتقال اطلاعات از اکسل به اکسس
دستور DoCmd.TransferSpreadsheet که در بالا به آن اشاره کردم که یادتان هست . قرار شد در جای خودش به توضیح عملکرد این دستور بپردازم . اینجا دقیقا همان جایی است که باید از این دستور صحبت کنیم.
این دستور قرار است کار مهمی را برای ما انجام دهد و آن هم انتقال اطلاعات مورد نظر ما از داخل نرم افزار اکسل به نرم افزار اکسسی که در حال حاضر بر روی سیستم ما وجود دارد .
پس لازم است که کمی وقت بگذاریم و بیشتر با این دستور و آرگومانهای آن آشنا شویم . در ابتدای کار فرمت کلی این دستور را به همراه تمامی آرگومانها و ورودی های مربوط به آن را در اینجا مشاهده می کنیم .
_TransferSpreadsheet (TransferType, SpreadsheetType
(TableName, FileName, HasFieldNames, Rang
اگر نگاهی به مجموع ورودی های این دستور بیندازیم متوجه می شویم که این دستور ۶ ورودی را می پذیرد . در اینجا به معرفی یک به یک هر کدام از ورودی های این دستور می پردازیم .
– آرگومان اول TransferType
در این ورودی می توانیم نوع انتقال اطلاعات خود را مشخص کنیم . اما منظور از نوع انتقال اطلاعات چیست ؟ با استفاده از آرگومان تعیین می کنیم که ایا ما می خواهیم اطلاعات را به اکسل وارد کنیم یا اینکه اطلاعات را از اکسل خارج کنیم .
خود این آرگومان می تواند یکی از سه حالت زیر را داشته باشد .
۱- acExport : اگر این گزینه را برای این آرگومان انتخاب کنیم می توانیم اطلاعات را از اکسل خارج کنیم .
۲- acImport :اگر این گزینه را برای این آرگومان انتخاب کنیم می توانیم اطلاعات را به اکسل وارد کنیم .
۳-acLink: اگر این گزینه را برای این آرگومان انتخاب کنیم می توانیم بین اطلاعات اکسل و یک منبع اطلاعاتی دیگر لینک برقرار کنیم.
– آرگومان دوم SpreadSheetType
این آرگومان تعیین می کند که فایل اکسلی که در حال حاضر در آن مشغول کار بر روی اطلاعات خود هستیم و قصد داریم اطلاعات آن را به فایل اکسس خود منتقل کنیم از چه نوعی است .
خود این ورودی می تواند شامل یکی از آیتم های زیر باشد .
– آرگومان سوم : Table Name
اسم این آرگومان گویای کاری است که می تواند برای ما انجام دهد . در این آرگومان اسم جدول مورد نظر خود را در اکسس که قرار است اطلاعات خود را در آن وارد کنیم به این دستور معرفی می کنیم.
اما اینجا یک سوال پیش می آید . اگر از قبل همچین جدولی در اکسس وجود نداشته باشد تکلیف چیست ؟ در این صورت برنامه جدولی تحت همین نام را برای ما ایجاد خواهد کرد .
آرگومان چهارم : FileName
این آرگومان بصورت یک متن است .در این متن نام فایل اکسل خود را به همراه آدرسی که در حال حاضر در آن ذخیره شده است را در اختیار این دستور می گذاریم.
در انتهای همین متن هم باید فرمت فرمت اکسل مورد نظر را برای برنامه مشخص کنیم .
آرگومان پنجم : HasFieldNames
این آرگومان دو حالت را می تواند بپذیرد . حالت اول مقدار True می گیرد . در این صورت ردیف اول از اطلاعات ما در اکسل عناوین فیلدهای ما را در جدول اکسس تشکیل خواهد داد.
حالت دوم مقدار False را می گیرد . در این حالت اطلاعات موجود در سطر اول هم مانند اطلاعات دیگر سطرها خواهد بود . .
آرگومان ششم : Range
در این آرگومان محدوده سلول های مورد نظر که می خواهیم اطلاعات آن را برای اکسس بفرستیم را به برنامه معرفی می کنیم .
در اینجا تمام آرگومان های مربوط به این دستور را با هم بررسی کردیم . در ادامه بصورت عملی از این دستور استفاده می کنیم که اطلاعات خود را به اکسس مورد نظر خود ارسال کنیم .
وقت کار عملی است .
حالا برویم ببینیم همین چیزهایی را که در تئوری خواندیم چطوری در عمل به ما کمک خواهند کرد .
برای شروع کار یک ماژول و در داخل ماژول خود یک روال ایجاد می کنیم . من در اینجا اسم روال خود را send worksheet در نظر می گیرم .
.
این مطلب به تدریج تکمیل خواهد شد .
مطالب زیر را حتما مطالعه کنید
Xlookup در اکسل- تابعی با کلی قابلیت های کاربردی
تابع Hlookup در اکسل💥نکات و ترفندهای کار با آن +ویدئو
تابع فیلتر در اکسل 💥به همراه یک مثال کاربردی
تابع سفارشی در اکسل 💥چطور تابع مدنظرتان را در اکسل ایجاد کنید ؟
تابع countif در اکسل💚راهنمای کامل کار با آن
زوم کردن در سلول های اکسل + ۳ روش مختلف
5 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام وقتتون به خیر
امکانش هست بقیه این آموزش را ایجاد بفرمایید
لطفا بقیه آموزش هم بزارید
با سلام مطالب سایت به تدریج بروزرسانی می شود در اولین فرصت این مطلب هم بروز رسانی و تکمیل خواهد شد
الان حداقل یکسال داره میگذره
پس کی میخواد بروزرسانی بشه
سلام بروز رسانی مطالب سایت بر اساس میزان تقاضا و همینطور میزان بازدیدی که هر مطلب در سایت دارد انجام می شود اما این مطلب هم در اولین فرصت بروز رسانی و تکمیل می شود